Skip to main content

Loading Data

Overview​

Speediful provides data loading capabilties via SOAP, Bulk v1 and Bulk v2 APIs. A unified interface is provided by the procedure SLAM_load which requires an @api parameter. Alternatively, users can directly call the various SLAM_load_<api_name>. All operations are logged to SLAM_Activity_Log

General Requirements for Loading Data​

The input table should have an Id column defined as an NCHAR(18) or NVARCHAR(18) to receive the Salesforce Id returned by Salesforce.

important

When loading data to Salesforce, if the SLAM_Message column exists, Speediful ignores any records in the input table where SLAM_Message = 'Operation Successful.'. This allows the same input table to be re-run after a partial failure as it will simply ignore any rows that were previously successful. This approach makes it simple for users to correct data issues in-situ without risk of creating duplicate records, while also keeping Salesforce data processing volumes efficient

Effect of Load Operations​

Every data load operation results in the following columns being added to the input table:

  • SLAM_Message - the end result of the operation: 'Operation Successful.' or an error message return by Salesforce
  • SLAM_unique_id (as needed) - a generated unique identifier if there is no available key column in the input table

The two SLAM_* fields are not loaded into the Salesforce object, but as responses are received from Salesforce, the SLAM_* fields are populated and the Id column is written with the Salesforce Id from the response.

Field Mapping​

Speediful automatically maps fields to Salesforce when the SQL column name matches an eligible field API name. Eligible fields will depend on the operation being performed (insert, update, delete, etc). Fields that exist in the input table but do not exist in the Salesforce target will be ignored and logged to the program's output without raising an error.

A common problem is that Salesforce fields do not get populated as expected even if they exist in the input table. This is usually caused by incomplete field level security configuration, where the user performing the data load does not have edit access to the fields that need to be populated.

Dual Relationship Syntax Support​

Speediful supports two relationship naming styles for upsert operations:

  • RelationshipName.Field (Salesforce standard style)
    • The RelationshipName is defined within the Salesforce API and can be found in the [part.RelationshipName] column of the sObject_fields_* table
    • Examples: Owner.Username and Contact_Lookup__r.External_Id__c
  • FieldApiName.Field (DBAmp compatibility style)
    • Speediful supports this naming style as a developer aid/convenience. Speediful automatically translates them into the RelationshipName style supported by Salesforce
    • Examples: OwnerId.Username and Contact_Lookup__c.External_Id__c

Client-side Disconnections​

SOAP operations require a continuous, uninterrupted connection with the Salesforce server while data is transferred in batches. Disconnections for any reason (server crash, network interruption, etc) can result in an inconsistent state between the database and Salesforce for the in-progress batch. A manual true-up of data would be required in this scenario.

Salesforce has designed the Bulk APIs to be asynchronous, which means that once the dataset has been transferred to Salesforce, an ongoing connection with Salesforce is not required. If a disconnection occurs after the data has been transferred to Salesforce, the result of the completed Salesforce processing can be retrieved using SLAM_download_job

A brief overview of the APIs​

  • Bulk v1 API provides the highest level of control over large data loads, including batch sizes and serial vs parallel processing. Bulk v1 supports hardDelete. Note that Salesforce specifically has not validated Bulk v1 for use with custom address fields and therefore you should look to Bulk v2 in those situations

  • Bulk v2 API provides a simplified approach to large data loads, allowing Salesforce to take a determining role in the orchestration of the job. Bulk v2 supports hardDelete

  • SOAP API is suited to smaller volumes of records and provides additional controls via header options (see SOAP Headers section). SOAP is the only API to support undelete